#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into oe_rpt.rpt_attendance
with a as (select att.date_code,
                  att.class_id,
                  att.content,
                  att.studying_student_count,
                  1                                     time_type,
                  coalesce(morning_attendance_count, 0) attendance_count,
                  coalesce(morning_attendance_ratio, 0) attendance_ratio,
                  coalesce(morning_late_count, 0)       late_count,
                  coalesce(morning_late_ratio, 0)       late_ratio,
                  coalesce(morning_leave_count, 0)      leave_count,
                  coalesce(morning_leave_ratio, 0)      leave_ratio,
                  coalesce(morning_absent_count, 0)     absent_count,
                  coalesce(morning_absent_ratio, 0)     absent_ratio
           from oe_dm.dm_attendance att
                    left join oe_dm.dm_leave le on att.class_id = le.class_id
                    left join oe_dm.dm_absent ab on att.class_id = ab.class_id
           union all
           select att.date_code,
                  att.class_id,
                  att.content,
                  att.studying_student_count,
                  2                                       time_type,
                  coalesce(afternoon_attendance_count, 0) attendance_count,
                  coalesce(afternoon_attendance_ratio, 0) attendance_ratio,
                  coalesce(afternoon_late_count, 0)       late_count,
                  coalesce(afternoon_late_ratio, 0)       late_ratio,
                  coalesce(afternoon_leave_count, 0)      leave_count,
                  coalesce(afternoon_leave_ratio, 0)      leave_ratio,
                  coalesce(afternoon_absent_count, 0)     absent_count,
                  coalesce(afternoon_absent_ratio, 0)     absent_ratio
           from oe_dm.dm_attendance att
                    left join oe_dm.dm_leave le on att.class_id = le.class_id
                    left join oe_dm.dm_absent ab on att.class_id = ab.class_id
           union all
           select att.date_code,
                  att.class_id,
                  att.content,
                  att.studying_student_count,
                  3                                     time_type,
                  coalesce(evening_attendance_count, 0) attendance_count,
                  coalesce(evening_attendance_ratio, 0) attendance_ratio,
                  coalesce(evening_late_count, 0)       late_count,
                  coalesce(evening_late_ratio, 0)       late_ratio,
                  coalesce(evening_leave_count, 0)      leave_count,
                  coalesce(evening_leave_ratio, 0)      leave_ratio,
                  coalesce(evening_absent_count, 0)     absent_count,
                  coalesce(evening_absent_ratio, 0)     absent_ratio
           from oe_dm.dm_attendance att
                    left join oe_dm.dm_leave le on att.class_id = le.class_id
                    left join oe_dm.dm_absent ab on att.class_id = ab.class_id
           order by date_code, class_id, time_type)
select date_code,
       class_id,
       content,
       studying_student_count,
       case time_type
           when 1
               then '上午'
           when 2
               then '下午'
           when 3
               then '晚上'
           end as time_type,
       attendance_count,
       attendance_ratio,
       late_count,
       late_ratio,
       leave_count,
       leave_ratio,
       absent_count,
       absent_ratio
from a
;"